using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace TestProject
{
	public class Copy
	{
		private int m_copy_ID = 0;
		private int m_book_ID = 0;
		private string m_callNo = "";
		private bool m_status = false;
		private StringBuilder m_borrower = new StringBuilder("");
		
		public Copy()
		{
		}
		
		public Copy(int copy_ID)
		{
			if (copy_ID!=0)
			{
				this.m_copy_ID = copy_ID;
				getData();
			}
		}
		
		public int copy_ID
		{
			set {
				m_copy_ID = value;
				getData();
			}
			get {
				return m_copy_ID;
			}
		}
		
		public int book_ID
		{
			set {
				m_book_ID = value;
			}
			get {
				return m_book_ID;
			}
		}
		
		public string callNo
		{
			set {
				m_callNo = value;
			}
			get {
				return m_callNo;
			}
		}
		
		public string borrower
		{
			set {
				m_borrower = new StringBuilder(value);
			}
			get {
				return m_borrower.ToString();
			}
		}
		
		public bool status
		{
			set{
				m_status = value;
			}
			get{
				return m_status;
			}
		}
		
		private void getData()
		{
	        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT book_ID, call_no "
					+ "FROM copy WHERE copy_id = '" + this.m_copy_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					m_book_ID = (int) reader["book_ID"];
					m_callNo = (string)reader["call_no"];
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public Reservation getCurrentReservation()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			Reservation toReturn = null;
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_ID "
					+ "FROM reservation WHERE copy_ID = '" + m_copy_ID + "'"
					+ "AND datestart >= '"+DateTime.Now.AddDays(-1)+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					toReturn = new Reservation((int)reader["reservation_ID"]);
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		public List<Reservation> getReservations()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Reservation> toReturn = new List<Reservation>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_ID "
					+ "FROM reservation WHERE copy_id = '" + m_copy_ID + "' ORDER BY start ASC";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					toReturn.Add(new Reservation((int) reader["reservation_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		/// <summary>
		/// Gets the current loan information of the copy. 
		/// </summary>
		/// <returns>
		/// A <see cref="Loan"/> The loan information in a Loan object.
		/// </returns>
		public Loan getCurrentLoan()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			Loan toReturn = null;
	        try
			{
				conn.Open();
				string statement = "SELECT loan_ID, datereturned "
					+ "FROM loan WHERE copy_ID = '" + m_copy_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					if (reader.IsDBNull(1))
					{
						m_status = true;
						toReturn = new Loan((int)reader["loan_ID"]);
						User dummy = new User(toReturn.user_ID);
						m_borrower.Append(dummy.username+", "+dummy.firstname+" "+dummy.lastname);
					}
					else
					{
						m_status = false;
					}
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		/// <summary>
		/// Gets all the loans of the copy. 
		/// </summary>
		/// <returns>
		/// A <see cref="List<Loan>"/> The loans in a List<Loan> object.
		/// </returns>
		public List<Loan> getLoans()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Loan> toReturn = new List<Loan>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT loan_ID "
					+ "FROM loan WHERE copy_id = '" + m_copy_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					toReturn.Add(new Loan((int) reader["loan_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		/// <summary>
		/// Gets the corresponding book of the copy. 
		/// </summary>
		/// <returns>
		/// A <see cref="Book"/> The book in a Book object.
		/// </returns>
		public Book getBook()
		{
			Book toReturn = new Book(m_book_ID);
			return toReturn;
		}
		
		/// <summary>
		/// Gets the corresponding book of the copy. 
		/// </summary>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the book.
		/// </param>
		/// <returns>
		/// A <see cref="Book"/> The book in a book object.
		/// </returns>
		public static Book getBook(int copy_ID)
		{
			Copy dummy = new Copy(copy_ID);
			return dummy.getBook();
		}
		
		/// <summary>
		/// Adds a copy of the book. 
		/// </summary>
		/// <param name="book_ID">
		/// A <see cref="System.Int32"/> The book_ID of the book.
		/// </param>
		public static void addCopy(int book_ID, string callNo)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "INSERT INTO copy (book_ID, call_no) "
					+ "VALUES ('"+book_ID+"','"+callNo+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		public static void editCopy(int copy_id, string callNo)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "UPDATE copy SET call_no='"+callNo+"'"
					+ " WHERE copy_id='"+copy_id+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Deletes a copy of a book. 
		/// </summary>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the copy to be deleted.
		/// </param>
		public static void deleteCopy(int copy_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();					
				string statement = "DELETE FROM copy where copy_ID='"+copy_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();				
				string newStatement = "DELETE FROM loan where copy_ID='"+copy_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();				
				string newStatementAgain = "DELETE FROM reservation where copy_ID='"+copy_ID+"'";
		        MySqlCommand newCmdAgain = new MySqlCommand(newStatementAgain, conn);
				newCmdAgain.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Gets the current reservation of the copy. 
		/// </summary>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the copy.
		/// </param>
		/// <returns>
		/// A <see cref="Reservation"/> The reservation in a Reservation object.
		/// </returns>
		public static Reservation getCurrentReservation(int copy_ID)
		{
	        return (new Copy(copy_ID)).getCurrentReservation();
		}
		
		/// <summary>
		/// Gets all the reservation of a copy of a book. 
		/// </summary>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the book.
		/// </param>
		/// <returns>
		/// A <see cref="List<Reservation>"/> The reservations in a List<Reservation> object.
		/// </returns>
		public static List<Reservation> getReservations(int copy_ID)
		{
			return (new Copy(copy_ID)).getReservations();
		}
		
		/// <summary>
		/// Gets the current loan information of a copy. 
		/// </summary>
		/// <param name="copy_ID">
		/// A <see cref="System.Int32"/> The copy_ID of the copy.
		/// </param>
		/// <returns>
		/// A <see cref="Loan"/> The loan information in a Loan object.
		/// </returns>
		public static Loan getCurrentLoan(int copy_ID)
		{
			return (new Copy(copy_ID)).getCurrentLoan();
		}
		
		/// <summary> 
		/// Gets all the loans of a copy. 
		/// </summary> 
		/// <param name="copy_ID"> 
		/// A <see cref="System.Int32"/> The copy_ID of the copy of the book. 
		/// </param> 
		/// <returns> 
		/// A <see cref="List<Loan>"/> The loans in a ListLoan object. 
		/// </returns> 
		
		public static List<Loan> getLoans(int copy_ID) 
		{ 
            return (new Copy(copy_ID)).getLoans(); 
        }
	}
}


